package edu.ucsd.cse135.gas.action.applicant;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import edu.ucsd.cse135.gas.logic.Degree;
import edu.ucsd.cse135.gas.logic.applicant.Applicant;
import edu.ucsd.cse135.gas.logic.support.Database;
import edu.ucsd.cse135.gas.resources.Constants;

public class OldApplicationAction extends Action{

	public OldApplicationAction() {
		super();
	}
	
	public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) {
		
		try {
			
			Connection conn = Database.open();
			
			String sql = "SELECT id FROM applicant WHERE mail = ?;";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			preStmt.setString(1, request.getRemoteUser());
			
			ResultSet rs = preStmt.executeQuery();
			
			rs.next();
			
			Applicant applicant = new Applicant();
			
			applicant.setApplicant(rs.getInt("id"));
			
			request.getSession().setAttribute("applicant", applicant);
			
			ArrayList<Degree> degreeList = new ArrayList<Degree>();
			
			sql = "SELECT degree.month AS month, degree.year AS year, degree.gpa AS gpa, discipline.name AS discipline, " +
				  "university.name AS university, location.name AS location, title.name AS title FROM degree, location, title, " +
				  "university, discipline WHERE degree.applicant = ? AND degree.university = university.id AND " +
				  "university.location = location.id AND degree.title = title.id AND discipline.id = degree.discipline;";
			
			preStmt = conn.prepareStatement(sql);
			preStmt.setInt(1, applicant.getApplicantId());
			rs = preStmt.executeQuery();
			
			while(rs.next())
			{
				Degree degree = new Degree();
				
				degree.setMonth(rs.getInt("month"));
				degree.setYear(rs.getInt("year"));
				degree.setGpa(rs.getFloat("gpa"));
				degree.setTitle(rs.getString("title"));
				degree.setLocation(rs.getString("location"));
				degree.setUniversity(rs.getString("university"));
				degree.setDiscipline(rs.getString("discipline"));
				
				degreeList.add(degree);
			}
			
			request.getSession().setAttribute("degrees", degreeList);
			
			return mapping.findForward(Constants.SUCCESS);	
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return null;
		}
		
	}
	
}

